package com.etc.dao.impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import DbConnect.DbConnect;

import com.etc.dao.IUserDao;
import com.etc.entity.*;

public class UserImpl implements IUserDao {
	private static String SELECT_SQL ="select * from users_info where username=? and password=?";
	private static String INSERT_SQL ="insert into users_info(username,password,role,status) values(?,?,?,?)";
	private static String UPDATE_SQL ="update users_info set username=?,password=?,role=?,status=? where id=?";
	private static String DELETE_SQL ="delete from users_info where id=?";
	
	//��¼
	@Override
	public User findUser(String name, String pwd,String role) {
		// TODO Auto-generated method stub
				Connection con=null;
			    PreparedStatement prepStmt=null;
			    ResultSet rs=null;
			    
			    try {
			    	con=(Connection) DbConnect.getDBconnection();
		            prepStmt = (PreparedStatement) con.prepareStatement(SELECT_SQL);
		            prepStmt.setString(1,name);
		            prepStmt.setString(2,pwd);
		            rs = (ResultSet) prepStmt.executeQuery();
		            if (rs.next()){
		            	User user=new User();
		            	user.setId(rs.getInt(1));
		            	user.setUsername(rs.getString(2));
		            	user.setPassword(rs.getString(3));
		            	user.setRole(rs.getString(4));
		            	user.setStatus(rs.getString(5));
		            	//�ж��˺��Ƿ񱻶��ᣬ�������޷���¼
		            	if(user.getStatus()=="���")
		            		user=null;
		            	return user;                  
		           }else {
		        	   return null ;
		           }
		      } catch (Exception e) {
		          // handle exception
		    	  return null;
		      } finally {
		    	  DbConnect.closeDB(con, prepStmt, rs);
		       }
	}

	//ע��
	@Override
	public Boolean regUser(String name, String pwd, String role, String status) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
				Connection con=null;
			    PreparedStatement prepStmt=null;
			    
			    try {
			    	con=(Connection) DbConnect.getDBconnection();
			    	String sql ="select * from users_info where username='"+name+"'";
			    	Statement stmt = con.createStatement();
					ResultSet rs = (ResultSet) stmt.executeQuery(sql);
					//�û����ظ�
					if(rs.next())
					{
						return false;
					}
					else
					{
		            prepStmt = (PreparedStatement) con.prepareStatement(INSERT_SQL);
		            prepStmt.setString(1,name);
		            prepStmt.setString(2,pwd);
		            prepStmt.setString(3,role);
		            prepStmt.setString(4,status);
		            prepStmt.executeUpdate();
		            return true;                  
		           }
		      } catch (Exception e) {
		          // handle exception
		    	  return false;
		      } finally {
				DbConnect.closeDB(con, prepStmt, null);
		       }
	}

	//ģ������
	@Override
	public List<User> searchUser(String search) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
				Connection con=null;
			    PreparedStatement prepStmt=null;
			    ResultSet rs=null;
			    List<User> userList = new ArrayList<User>();
			    
			    try {
			    	con=(Connection) DbConnect.getDBconnection();
			    	String sql = "";
			    	if(search==null||"".equals(search))
			    		sql="select * from users_info  ORDER BY id asc";
			    	else
			    		sql="select * from users_info where username like'%"+search+"%' ORDER BY id asc";
		            prepStmt = (PreparedStatement) con.prepareStatement(sql);
		            rs = (ResultSet) prepStmt.executeQuery();
		            while(rs.next()){
		            	User user=new User();
		            	user.setId(rs.getInt(1));
		            	user.setUsername(rs.getString(2));
		            	user.setPassword(rs.getString(3));
		            	user.setRole(rs.getString(4));
		            	user.setStatus(rs.getString(5));
		            	userList.add(user); 
		            	
		           }
		      } catch (Exception e) {
		          // handle exception
		    	  return null;
		      } finally {
		    	  DbConnect.closeDB(con, prepStmt, rs);
		       }
			    return userList;
	}

	//�޸��û�����
	@Override
	public Boolean updateUser(String username,String password,String newpassword) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
				Connection con=null;
			    PreparedStatement prepStmt=null;
			    ResultSet rs=null;
			    
			    try {
			    	con=(Connection) DbConnect.getDBconnection();
			    	String ssql="select * from users_info where username=? and password=?";
			    	prepStmt = (PreparedStatement) con.prepareStatement(ssql);
		            prepStmt.setString(1, username);
		            prepStmt.setString(2,password);
		            rs = (ResultSet) prepStmt.executeQuery();
			    	if(rs.next())
			    	{
			    		String sql="update users_info set password=? where username=? and password=?";
			    		prepStmt = (PreparedStatement) con.prepareStatement(sql);
			    		prepStmt.setString(1,newpassword);
			    		prepStmt.setString(2, username);
			    		prepStmt.setString(3,password);
			    		int i =prepStmt.executeUpdate();
			    		if (i>0){
			    			return true;                  
			    		}else {
			    			return false ;
			    		}			    		
			    	}
			    	else
			    		return false;
		      } catch (Exception e) {
		          // handle exception
		    	  return false;
		      } finally {
		    	  DbConnect.closeDB(con, prepStmt, rs);
		       }
	}

	//����û�
	@Override
	public Boolean closeUser(int id) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		Connection con=null;
	    PreparedStatement prepStmt=null;
	    ResultSet rs=null;
	    
	    try {
	    	con=(Connection) DbConnect.getDBconnection();
	    	String sql="update users_info set status=? where id=?";
            prepStmt = (PreparedStatement) con.prepareStatement(sql);
            prepStmt.setString(1,"���");
            prepStmt.setInt(2,id);
            int i =prepStmt.executeUpdate();
            if (i>0){
            	return true;                  
           }else {
        	   return false ;
           }
      } catch (Exception e) {
          // handle exception
    	  return null;
      } finally {
    	  DbConnect.closeDB(con, prepStmt, rs);
       }
	}

	//���
	@Override
	public Boolean openUser(int id) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
				Connection con=null;
			    PreparedStatement prepStmt=null;
			    ResultSet rs=null;
			    
			    try {
			    	con=(Connection) DbConnect.getDBconnection();
			    	String sql="update users_info set status=? where id=?";
		            prepStmt = (PreparedStatement) con.prepareStatement(sql);
		            prepStmt.setString(1,"����");
		            prepStmt.setInt(2,id);
		            int i =prepStmt.executeUpdate();
		            if (i>0){
		            	return true;                  
		           }else {
		        	   return false ;
		           }
		      } catch (Exception e) {
		          // handle exception
		    	  return null;
		      } finally {
		    	  DbConnect.closeDB(con, prepStmt, rs);
		       }
	}

	/////////////////////////////////////////
	
	//ɾ���û�
	@Override
	public Boolean deleteUser(int id) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
				Connection con=null;
			    PreparedStatement prepStmt=null;
			    ResultSet rs=null;
			    
			    try {
			    	con=(Connection) DbConnect.getDBconnection();
		            prepStmt = (PreparedStatement) con.prepareStatement(DELETE_SQL);
		            prepStmt.setInt(1,id);
		            int i =prepStmt.executeUpdate();
		            if (i>0){
		            	return true;                  
		           }else {
		        	   return false ;
		           }
		      } catch (Exception e) {
		          // handle exception
		    	  return false;
		      } finally {
		    	  DbConnect.closeDB(con, prepStmt, rs);
		       }
	}

	//��ѯ�����û�
	@Override
	public List<User> searchAllUser() {
		// TODO Auto-generated method stub
		Connection con=null;
	    PreparedStatement prepStmt=null;
	    ResultSet rs=null;
	    List<User> userList = new ArrayList<User>();
	    
	    try {
	    	con=(Connection) DbConnect.getDBconnection();
	    	String SEARCH_SQL="select * from users_info ";
            prepStmt = (PreparedStatement) con.prepareStatement(SEARCH_SQL);
            rs = (ResultSet) prepStmt.executeQuery();
            while(rs.next()){
            	User user=new User();
            	user.setId(rs.getInt(1));
            	user.setUsername(rs.getString(2));
            	user.setPassword(rs.getString(3));
            	user.setRole(rs.getString(4));
            	user.setStatus(rs.getString(5));
            	userList.add(user); 
            	
           }
      } catch (Exception e) {
          // handle exception
    	  return null;
      } finally {
    	  DbConnect.closeDB(con, prepStmt, rs);
       }
	    return userList;
	}

}
